﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI.DataVisualization.Charting;
using System.Web.UI;
using C3.Proto.Core.Model;
using C3.Proto.Core.Service;
using C3.Proto.Repo;
using C3.Safety.Service;
using EvoPdf.HtmlToPdf;
using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace C3.Proto.Web.Controllers
{
    
    public class HomeController : BaseController
    {
        public string LastRunDate;
        
        public ActionResult Index()
        {
            var usr = SessionService.CurrentUser;
            var h = new List<JobHistory>();
            LastRunDate = string.Empty;
            if (!bool.Parse(ConfigurationManager.AppSettings["Debug"]))
            {
                h = SafetyService.GetJobHistory().OrderByDescending(t => t.Date).ToList();
                if (h.Count > 0)
                {
                    var g = new Guid("5842274F-99CA-4F31-9A56-DB0018A6E11A");
                    var last = h.Where(t => t.Id == g).OrderByDescending(t => t.Date).First();
                    if (last != null)
                        LastRunDate = last.Date.ToString();
                }
            }
            //Sort the projects 0-9, A-Z
            var p = (List<Project>) SafetyService.GetProjects();
            p.Sort((x, y) => string.Compare(x.Name, y.Name));
            ViewData["User"] = usr;
            
            ViewData["History"] = h;
            ViewData["LastRunDate"] = LastRunDate;
            ViewData["Projects"] = p;
            return View();
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult AddCSV(int id, string doctype)
        {
            UserService.AddCSV(id, doctype);
            return RedirectToAction("Index");

        }
        public void RegionExport(string region, string startmonth, string startyear, string endmonth, string endyear, string mode)
        {
            var start = new DateTime(Convert.ToInt32(startyear), Convert.ToInt32(startmonth), 1);
            var end = new DateTime(Convert.ToInt32(endyear), Convert.ToInt32(endmonth), 1);
            end = end.AddMonths(1);
            var reg = region.Remove(0, 3);
            var results = SafetyService.GetRegionResults(region, start, end);
            var pck = new ExcelPackage();
            var ws = pck.Workbook.Worksheets.Add("RegionReport");
            ws.Column(1).Width = 6;
            ws.Column(2).Width = 13;
            ws.Column(3).Width = 50;
            ws.Column(4).Width = 10;
            ws.Column(5).Width = 30;
            ws.Column(6).Width = 16;
            ws.defaultRowHeight = 15;
            ws.PrinterSettings.RightMargin = .2M;
            ws.PrinterSettings.LeftMargin = .3M;
            ws.PrinterSettings.TopMargin = .5M;
            ws.PrinterSettings.BottomMargin = .25M;
            
            ws.Cells["A:Q"].Style.Font.Size = 7;
            ws.Cells[1, 2].Value = "Region Report";
            ws.Cells[1, 1].Style.Font.Bold = true;

            ws.Cells[2, 2].Value = "Region";
            ws.Cells[2, 2].Style.Font.Bold = true;
            ws.Cells[2, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[2, 3].Value = "Project Names and Site Number";
            ws.Cells[2, 3].Style.Font.Bold = true;
            ws.Cells[2, 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[2, 4].Value = "GC Inspections";
            ws.Cells[2, 4].Style.Font.Bold = true;

            var row = 3;

            for (var i = 0; i < results.GCProjects.Count; i++)
            {
                var column = 2;
                ws.Cells[row, column++].Value = results.GCProjects[i].Institute;
                ws.Cells[row, column++].Value = results.GCProjects[i].Project;
                ws.Cells[row, column++].Value = results.GCProjects[i].Inspections + "%";
                row++;

            }

            row = (row > 24) ? (39 - row % 38) + row : 25;
            

            var Chart3 = new System.Web.UI.DataVisualization.Charting.Chart
            {
                Width = 389,
                Height = 216,
                RenderType = RenderType.ImageTag,
                ImageLocation = "..\\..\\TempImages\\ChartPic_#SEQ(200,30)",
                Palette = ChartColorPalette.BrightPastel
            };
            Chart3.ChartAreas.Add("Area");
            Chart3.Series.Add("Series");
            Chart3.Series["Series"].ChartType = SeriesChartType.Pie;
            Chart3.Series["Series"]["PieLabelStyle"] = "Inside";
            Chart3.Series["Series"].Label = "#VALY";
            Chart3.Series["Series"].LegendText = "#VALY - #VALX";

            // Display a Title
            Chart3.Titles.Add(string.Format("{0} Safety Deficiencies", reg));
            // Add Data to Display
            Chart3.Series["Series"].Points.DataBindXY(results.DefeciencyTypes.Keys, results.DefeciencyTypes.Values);

            // Call Out The Letter "D"


            // Display a Legend
            Chart3.Legends.Add(new Legend("Deficiencies"));
            Chart3.Legends["Deficiencies"].Title = "Deficiencies";

            Chart3.Series["Series"].Legend = "Deficiencies";

            var stream = new System.IO.MemoryStream();

            // Render chart control
            Chart3.SaveImage(stream);



            var image = ws.Drawings.AddPicture("chart2", Image.FromStream(stream));
            image.From.Column = 0;
            image.From.Row = row;
            image.SetSize(389, 216);


            var Chart2 = new System.Web.UI.DataVisualization.Charting.Chart
            {
                Width = 389,
                Height = 216,
                RenderType = RenderType.ImageTag,
                ImageLocation = "..\\..\\TempImages\\ChartPic_#SEQ(200,30)",
                Palette = ChartColorPalette.BrightPastel
            };

            var t = new Title(string.Format("{0} Safety Deficiencies", reg), Docking.Top, new System.Drawing.Font("Trebuchet MS", 9, System.Drawing.FontStyle.Bold), System.Drawing.Color.FromArgb(26, 59, 105));
            Chart2.Titles.Add(t);
            Chart2.ChartAreas.Add("ChartArea1");

            // Enable X axis labels automatic fitting
            Chart2.ChartAreas["ChartArea1"].AxisX.IsLabelAutoFit = true;
            Chart2.ChartAreas["ChartArea1"].AxisX.TextOrientation = TextOrientation.Rotated90;
            // Set X axis automatic fitting style
            Chart2.ChartAreas["ChartArea1"].AxisX.LabelAutoFitStyle =
                 LabelAutoFitStyles.DecreaseFont | LabelAutoFitStyles.IncreaseFont | LabelAutoFitStyles.WordWrap;
            // create a couple of series
            Chart2.Series.Add("GC");
            Chart2.Series.Add("OFPC");
            Chart2.Series.Add("ROCIP");


            Chart2.ChartAreas["ChartArea1"].AxisX.IntervalAutoMode = IntervalAutoMode.FixedCount;
            Chart2.ChartAreas["ChartArea1"].AxisX.Interval = 1;





            Chart2.Series["GC"].Points.AddY(results.GCSeverityA);
            Chart2.Series["GC"].IsValueShownAsLabel = false;

            Chart2.Series["OFPC"].Points.AddY(results.OFPCSeverityA);
            Chart2.Series["OFPC"].Points[0].AxisLabel = "A";
            Chart2.Series["OFPC"].IsValueShownAsLabel = false;

            Chart2.Series["ROCIP"].Points.AddY(results.ROCIPSeverityA);
            Chart2.Series["ROCIP"].IsValueShownAsLabel = false;

            Chart2.Series["GC"].Points.AddY(results.GCSeverityB);
            Chart2.Series["GC"].IsValueShownAsLabel = false;

            Chart2.Series["OFPC"].Points.AddY(results.OFPCSeverityB);
            Chart2.Series["OFPC"].Points[1].AxisLabel = "B";
            Chart2.Series["OFPC"].IsValueShownAsLabel = false;

            Chart2.Series["ROCIP"].Points.AddY(results.ROCIPSeverityB);
            Chart2.Series["ROCIP"].IsValueShownAsLabel = false;

            Chart2.Series["GC"].Points.AddY(results.GCSeverityC);
            Chart2.Series["GC"].IsValueShownAsLabel = false;

            Chart2.Series["OFPC"].Points.AddY(results.OFPCSeverityC);
            Chart2.Series["OFPC"].Points[2].AxisLabel = "C";
            Chart2.Series["OFPC"].IsValueShownAsLabel = false;

            Chart2.Series["ROCIP"].Points.AddY(results.ROCIPSeverityC);
            Chart2.Series["ROCIP"].IsValueShownAsLabel = false;


            Chart2.Series["GC"].Points.AddY(results.GCSeverityD);
            Chart2.Series["GC"].IsValueShownAsLabel = false;

            Chart2.Series["OFPC"].Points.AddY(results.OFPCSeverityD);
            Chart2.Series["OFPC"].Points[3].AxisLabel = "D";
            Chart2.Series["OFPC"].IsValueShownAsLabel = false;

            Chart2.Series["ROCIP"].Points.AddY(results.ROCIPSeverityD);
            Chart2.Series["ROCIP"].IsValueShownAsLabel = false;

            Chart2.BorderSkin.SkinStyle = BorderSkinStyle.None;
            Chart2.BorderWidth = 0;

            Chart2.Legends.Add("Legend1");

            // show legend based on check box value
            Chart2.Legends["Legend1"].Enabled = true;

            // Render chart control
            stream = new System.IO.MemoryStream();

            // Render chart control
            Chart2.SaveImage(stream);

            

            image = ws.Drawings.AddPicture("chart1", Image.FromStream(stream));
            image.From.Column = 3;
            image.From.Row = row;
            image.SetSize(389, 216);

            row = (39 - row % 38) + row;

           
            ws.Cells[row, 1].Value = "Claims";
            ws.Cells[row, 1].Style.Font.Bold = true;
            ws.Cells[row, 2].Value = "Date of Loss";
            ws.Cells[row, 2].Style.Font.Bold = true;
            ws.Cells[row, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 3].Value = "Accident Narrative";
            ws.Cells[row, 3].Style.Font.Bold = true;
            ws.Cells[row, 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 4].Value = "Project #";
            ws.Cells[row, 4].Style.Font.Bold = true;
            ws.Cells[row, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 5].Value = "Site";
            ws.Cells[row, 5].Style.Font.Bold = true;
            ws.Cells[row, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 6].Value = "Nature of Injury";
            ws.Cells[row, 6].Style.Font.Bold = true;
            ws.Cells[row, 6].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            
            row++;

            for (var i = 0; i < results.Claims.Count; i++)
            {
                var column = 1;
                ws.Cells[row, column].Value = i;
                ws.Cells[row, column++].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                ws.Cells[row, column].Value = results.Claims[i].DateofLoss.ToShortDateString();
                ws.Cells[row, column++].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                ws.Cells[row, column++].Value = results.Claims[i].AccidentNarrative;
                ws.Cells[row, column++].Value = results.Claims[i].SiteLevel1;
                ws.Cells[row, column++].Value = results.Claims[i].SiteLevel1Name;
                ws.Cells[row, column++].Value = results.Claims[i].NCCINatureofInjury;
                row++;

            }

            Response.BinaryWrite(pck.GetAsByteArray());
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=Region.xlsx");

        }


        public void StateExport(string startmonth, string startyear, string endmonth, string endyear, string mode)
        {
            var start = new DateTime(Convert.ToInt32(startyear), Convert.ToInt32(startmonth), 1);
            var end = new DateTime(Convert.ToInt32(endyear), Convert.ToInt32(endmonth), 1);
            end = end.AddMonths(1);
            var results = SafetyService.GetStateReults(start, end);
            var pck = new ExcelPackage();
            var ws = pck.Workbook.Worksheets.Add("Statewide");
            ws.Cells["A:Q"].Style.Font.Size = 7;
            
    
            ws.defaultColWidth = 15;
            
            ws.Column(1).Width = 3;
            ws.Column(2).Width = 9;
            ws.Column(3).Width = 80;
            ws.Column(4).Width = 11;
            ws.Column(5).Width = 11;
            ws.Column(6).Width = 11;
            ws.defaultRowHeight = 15;
            ws.Cells[1,3].Value = "Statewide Report";
            ws.Cells[1,3].Style.Font.Bold = true;

            ws.Cells[2,3].Value = "Monthly Inspection Count";
            ws.Cells[2, 3].Style.Font.Bold = true;
            ws.Cells[3,3].Value = string.Format("GC Team: {0} inspections recorded from {1} projects.",results.GCCount,results.GCProjects);
            ws.Cells[4,3].Value = string.Format("OFPC Team: {0} inspections recorded from {1} projects.", results.OFPCCount, results.OFPCProjects);
            ws.Cells[5,3].Value = string.Format("ROCIP Team: {0} inspections recorded from {1} projects.", results.ROCIPCount, results.ROCIPProjects);

            ws.Cells[8,3].Value = "Combined Defecient Condition Categories";
            ws.Cells[8, 3].Style.Font.Bold = true;
            ws.Cells[9,3].Value = "Statewide Deficiency Category";
            ws.Cells[9, 3].Style.Font.Bold = true;
            ws.Cells[9,4].Value = "OFPC Team";
            ws.Cells[9, 4].Style.Font.Bold = true;
            ws.Cells[9, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; 
            ws.Cells[9,5].Value = "ROCIP Team";
            ws.Cells[9, 5].Style.Font.Bold = true;
            ws.Cells[9, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; 
            ws.Cells[9,6].Value = "GC Team";
            ws.Cells[9, 6].Style.Font.Bold = true;
            ws.Cells[9, 6].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; 

            var row = 10;
            
            for (var i = 0; i < results.CategoryDeficiencies.Count; i++ )
            {
                var column = 3;
                ws.Cells[row, column++].Value = results.CategoryDeficiencies[i].Category;
                ws.Cells[row, column].Value = results.CategoryDeficiencies[i].OFPC + "%";
                ws.Cells[row, column++].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                ws.Cells[row, column].Value = results.CategoryDeficiencies[i].ROCIP + "%";
                ws.Cells[row, column++].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                ws.Cells[row, column].Value = results.CategoryDeficiencies[i].GC + "%";
                ws.Cells[row, column++].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                row++;

            }
            ws.Cells[row, 3].Value = "Total";
            ws.Cells[row, 4].Value = Math.Round(results.CategoryDeficiencies.Sum(x => x.OFPC)) + "%";
            ws.Cells[row, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 5].Value = Math.Round(results.CategoryDeficiencies.Sum(x => x.ROCIP)) + "%";
            ws.Cells[row, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 6].Value = Math.Round(results.CategoryDeficiencies.Sum(x => x.GC)) + "%";
            ws.Cells[row, 6].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            row++;


            var Chart1 = new System.Web.UI.DataVisualization.Charting.Chart
            {
                Width = 750,
                Height = 500,
                RenderType = RenderType.ImageTag,
                ImageLocation = "..\\..\\TempImages\\ChartPic_#SEQ(200,30)",
                Palette = ChartColorPalette.BrightPastel
              
            };
          

            
            var t = new Title("Statewide Deficiency Category", Docking.Top, new System.Drawing.Font("Trebuchet MS", 11, System.Drawing.FontStyle.Bold), System.Drawing.Color.FromArgb(26, 59, 105));
            Chart1.Titles.Add(t);
            
            Chart1.ChartAreas.Add("ChartArea1");
            
            // Enable X axis labels automatic fitting
            Chart1.ChartAreas["ChartArea1"].AxisX.IsLabelAutoFit = true;
            Chart1.ChartAreas["ChartArea1"].AxisX.TextOrientation = TextOrientation.Rotated90;
            // Set X axis automatic fitting style
            Chart1.ChartAreas["ChartArea1"].AxisX.LabelAutoFitStyle =
                LabelAutoFitStyles.DecreaseFont | LabelAutoFitStyles.IncreaseFont | LabelAutoFitStyles.WordWrap;
            // create a couple of series
            Chart1.Series.Add("GC");
            Chart1.Series.Add("OFPC");
            Chart1.Series.Add("ROCIP");

            var cat = (IList<Deficiencies>)results.CategoryDeficiencies;
            Chart1.ChartAreas["ChartArea1"].AxisX.IntervalAutoMode = IntervalAutoMode.FixedCount;
            Chart1.ChartAreas["ChartArea1"].AxisX.Interval = 1;




            for (var i = 0; i < cat.Count; i++)
            {


                Chart1.Series["GC"].Points.AddY(cat[i].GC);

                Chart1.Series["GC"].IsValueShownAsLabel = false;

                Chart1.Series["OFPC"].Points.AddY(cat[i].OFPC);
                Chart1.Series["OFPC"].Points[i].AxisLabel = cat[i].Category;
                Chart1.Series["OFPC"].Points[i].LabelAngle = 30;


                Chart1.Series["OFPC"].IsValueShownAsLabel = false;

                Chart1.Series["ROCIP"].Points.AddY(cat[i].ROCIP);
                Chart1.Series["ROCIP"].IsValueShownAsLabel = false;
            }


            Chart1.BorderSkin.SkinStyle = BorderSkinStyle.None;
            Chart1.BorderWidth = 0;

            Chart1.Legends.Add("Legend1");

            // show legend based on check box value
            Chart1.Legends["Legend1"].Enabled = true;

            var stream = new System.IO.MemoryStream();

            // Render chart control
            Chart1.SaveImage(stream);


            row = 34;
            var image = ws.Drawings.AddPicture("chart1", Image.FromStream(stream));
            image.From.Column = 2;
            image.From.Row = row;
            image.SetSize(675, 450);

            var Chart2 = new System.Web.UI.DataVisualization.Charting.Chart
            {
                Width = 750,
                Height = 500,
                RenderType = RenderType.ImageTag,
                ImageLocation = "..\\..\\TempImages\\ChartPic_#SEQ(200,30)",
                Palette = ChartColorPalette.BrightPastel
            };

            t = new Title("Statewide Severity Levels", Docking.Top, new System.Drawing.Font("Trebuchet MS", 11, System.Drawing.FontStyle.Bold), System.Drawing.Color.FromArgb(26, 59, 105));
            Chart2.Titles.Add(t);
            Chart2.ChartAreas.Add("ChartArea1");

            // Enable X axis labels automatic fitting
            Chart2.ChartAreas["ChartArea1"].AxisX.IsLabelAutoFit = true;
            Chart2.ChartAreas["ChartArea1"].AxisX.TextOrientation = TextOrientation.Rotated90;
            // Set X axis automatic fitting style
            Chart2.ChartAreas["ChartArea1"].AxisX.LabelAutoFitStyle =
                 LabelAutoFitStyles.DecreaseFont | LabelAutoFitStyles.IncreaseFont | LabelAutoFitStyles.WordWrap;
            // create a couple of series
            Chart2.Series.Add("GC");
            Chart2.Series.Add("OFPC");
            Chart2.Series.Add("ROCIP");


            Chart2.ChartAreas["ChartArea1"].AxisX.IntervalAutoMode = IntervalAutoMode.FixedCount;
            Chart2.ChartAreas["ChartArea1"].AxisX.Interval = 1;

            Chart2.Series["GC"].Points.AddY(results.GCSeverityA);
            Chart2.Series["GC"].IsValueShownAsLabel = false;

            Chart2.Series["OFPC"].Points.AddY(results.OFPCSeverityA);
            Chart2.Series["OFPC"].Points[0].AxisLabel = "A";
            Chart2.Series["OFPC"].IsValueShownAsLabel = false;

            Chart2.Series["ROCIP"].Points.AddY(results.ROCIPSeverityA);
            Chart2.Series["ROCIP"].IsValueShownAsLabel = false;

            Chart2.Series["GC"].Points.AddY(results.GCSeverityB);
            Chart2.Series["GC"].IsValueShownAsLabel = false;

            Chart2.Series["OFPC"].Points.AddY(results.OFPCSeverityB);
            Chart2.Series["OFPC"].Points[1].AxisLabel = "B";
            Chart2.Series["OFPC"].IsValueShownAsLabel = false;

            Chart2.Series["ROCIP"].Points.AddY(results.ROCIPSeverityB);
            Chart2.Series["ROCIP"].IsValueShownAsLabel = false;

            Chart2.Series["GC"].Points.AddY(results.GCSeverityC);
            Chart2.Series["GC"].IsValueShownAsLabel = false;

            Chart2.Series["OFPC"].Points.AddY(results.OFPCSeverityC);
            Chart2.Series["OFPC"].Points[2].AxisLabel = "C";
            Chart2.Series["OFPC"].IsValueShownAsLabel = false;

            Chart2.Series["ROCIP"].Points.AddY(results.ROCIPSeverityC);
            Chart2.Series["ROCIP"].IsValueShownAsLabel = false;


            Chart2.Series["GC"].Points.AddY(results.GCSeverityD);
            Chart2.Series["GC"].IsValueShownAsLabel = false;

            Chart2.Series["OFPC"].Points.AddY(results.OFPCSeverityD);
            Chart2.Series["OFPC"].Points[3].AxisLabel = "D";
            Chart2.Series["OFPC"].IsValueShownAsLabel = false;

            Chart2.Series["ROCIP"].Points.AddY(results.ROCIPSeverityD);
            Chart2.Series["ROCIP"].IsValueShownAsLabel = false;

            Chart2.BorderSkin.SkinStyle = BorderSkinStyle.None;
            Chart2.BorderWidth = 0;

            Chart2.Legends.Add("Legend1");

            // show legend based on check box value
            Chart2.Legends["Legend1"].Enabled = true;

            var stream2 = new System.IO.MemoryStream();
            Chart2.SaveImage(stream2);
            row = 57;

            image = ws.Drawings.AddPicture("chart2", Image.FromStream(stream2));
            image.From.Column = 2;
            image.From.Row = row;
            image.SetSize(322, 215);

            row = 71;
            ws.Cells[row,3].Value = "General Work Activity Defecient Condition Categories";
            ws.Cells[row,3].Style.Font.Bold = true;
            
            row++;
            
            ws.Cells[row, 3].Value = "General Work Activity Defecient Condition Categories";
            ws.Cells[row, 3].Style.Font.Bold = true;
            ws.Cells[row, 4].Value = "OFPC Team";
            ws.Cells[row, 4].Style.Font.Bold = true;
            ws.Cells[row, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; 
            ws.Cells[row, 5].Value = "ROCIP Team";
            ws.Cells[row, 5].Style.Font.Bold = true;
            ws.Cells[row, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; 
            ws.Cells[row, 6].Value = "GC Team";
            ws.Cells[row, 6].Style.Font.Bold = true;
            ws.Cells[row, 6].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; 
            
            row++;

            for (var i = 0; i < results.GeneralDeficiencies.Count; i++)
            {
                var column = 3;
                ws.Cells[row, column++].Value = results.GeneralDeficiencies[i].Category;
                ws.Cells[row, column].Value = results.GeneralDeficiencies[i].OFPC + "%";
                ws.Cells[row, column++].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; 
                ws.Cells[row, column].Value = results.GeneralDeficiencies[i].ROCIP + "%";
                ws.Cells[row, column++].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; 
                ws.Cells[row, column].Value = results.GeneralDeficiencies[i].GC + "%";
                ws.Cells[row, column++].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; 
                row++;

            }

            ws.Cells[row, 3].Value = "Total";
            ws.Cells[row, 4].Value = Math.Round(results.GeneralDeficiencies.Sum(x => x.OFPC)) + "%";
            ws.Cells[row, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 5].Value = Math.Round(results.GeneralDeficiencies.Sum(x => x.ROCIP)) + "%";
            ws.Cells[row, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 6].Value = Math.Round(results.GeneralDeficiencies.Sum(x => x.GC)) + "%";
            ws.Cells[row, 6].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            row++;

           

            row = 103;

            ws.Cells[row, 1].Value = "Statewide Claims Reported";
            ws.Cells[row, 1].Style.Font.Bold = true;
            row++;
            row++;
            ws.Cells[row, 1].Value = "#";
            ws.Cells[row, 1].Style.Font.Bold = true;
            ws.Cells[row, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 2].Value = "Date of Loss";
            ws.Cells[row, 2].Style.Font.Bold = true;
            ws.Cells[row, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 3].Value = "Accident Narrative";
            ws.Cells[row, 3].Style.Font.Bold = true;
            ws.Cells[row, 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 4].Value = "Project #";
            ws.Cells[row, 4].Style.Font.Bold = true;
            ws.Cells[row, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            ws.Cells[row, 5].Value = "Nature of Injury";
            ws.Cells[row, 5].Style.Font.Bold = true;
            ws.Cells[row, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            
            row++;
            
            for (var i = 0; i < results.Claims.Count; i++ )
            {
                var column = 1;
                ws.Cells[row, column].Value = i + 1;
                ws.Cells[row, column++].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                ws.Cells[row, column].Value = results.Claims[i].DateofLoss.ToShortDateString();
                ws.Cells[row, column++].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                ws.Cells[row, column++].Value = results.Claims[i].AccidentNarrative;
                ws.Cells[row, column++].Value = results.Claims[i].SiteLevel1;
                ws.Cells[row, column++].Value = results.Claims[i].NCCINatureofInjury;
                row++;

            }

            row++;

            var Chart3 = new System.Web.UI.DataVisualization.Charting.Chart
            {
                Width = 750,
                Height = 500,
                RenderType = RenderType.ImageTag,
                ImageLocation = "..\\..\\TempImages\\ChartPic_#SEQ(200,30)",
                Palette = ChartColorPalette.BrightPastel
            };
            Chart3.ChartAreas.Add("Area");
            Chart3.Series.Add("Series");
            Chart3.Series["Series"].ChartType = SeriesChartType.Pie;
            Chart3.Series["Series"]["PieLabelStyle"] = "Inside";
            Chart3.Series["Series"].Label = "#VALY";
            Chart3.Series["Series"].LegendText = "#VALY - #VALX";

            // Display a Title
            Chart3.Titles.Add("Statewide Injury Types");

            // Add Data to Display

            Chart3.Series["Series"].Points.DataBindXY(results.Injuries.Keys, results.Injuries.Values);

            // Call Out The Letter "D"


            // Display a Legend
            Chart3.Legends.Add(new Legend("Injuries"));
            Chart3.Legends["Injuries"].Title = "Injuries";

            Chart3.Series["Series"].Legend = "Injuries";

            var stream3 = new System.IO.MemoryStream();
            Chart3.SaveImage(stream3);

            row = (35 - row % 34) + row;
            image = ws.Drawings.AddPicture("chart3", Image.FromStream(stream3));
            image.From.Column = 2;
            image.From.Row = row;
            image.SetSize(750, 500);

            Response.BinaryWrite(pck.GetAsByteArray());
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=Statewide.xlsx");
        }

       
        public void ProjectExport(string project, string startmonth, string startyear, string endmonth, string endyear, string mode)
        {


            var start = new DateTime(Convert.ToInt32(startyear), Convert.ToInt32(startmonth), 1);
            var end = new DateTime(Convert.ToInt32(endyear), Convert.ToInt32(endmonth), 1);
            end = end.AddMonths(1);
            var pck = new ExcelPackage();
            var current = string.Empty;
            

            try
            {
                
                var months = new List<string>();
                var starter = start;
                while (end > starter)
                {
                    months.Add(string.Format("{0}-{1}", starter.ToString("MMM"), starter.ToString("yy")));
                    starter = starter.AddMonths(1);
                }

                    
                    current = project;
                    var results = SafetyService.GetProjectInspections(project, start, end);
                    if (results == null)
                    {
                        Response.Write("No data found.");
                        Response.Write("<br/>");
                        Response.Write("Project: " + current);
                        return;
                    }
                var p = SafetyService.GetProjectByName(project);
                FillProjectWorksheet(pck, p, results, months, start, end);
                
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
                Response.Write("<br/>");
                Response.Write("Project: " + current);
                Response.End();
            }
            Response.BinaryWrite(pck.GetAsByteArray());
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=ProjectsReport.xlsx");
        }
        public void AllProjectsExport(string startmonth, string startyear, string endmonth, string endyear, string mode)
        {
           
            
            var start = new DateTime(Convert.ToInt32(startyear), Convert.ToInt32(startmonth), 1);
            var end = new DateTime(Convert.ToInt32(endyear), Convert.ToInt32(endmonth), 1);
            end = end.AddMonths(1);
            var pck = new ExcelPackage();
            var current = string.Empty;

            LastRunDate = string.Empty;
            var h = new List<JobHistory>();
            if (!bool.Parse(ConfigurationManager.AppSettings["Debug"]))
            {
                h = SafetyService.GetJobHistory().OrderByDescending(t => t.Date).ToList();
                if (h.Count > 0)
                {
                    var g = new Guid("5842274F-99CA-4F31-9A56-DB0018A6E11A");
                    var last = h.Where(t => t.Id == g).OrderByDescending(t => t.Date).First();
                    if (last != null)
                        LastRunDate = last.Date.ToString();
                }
            }
            try
            {
                var months = new List<string>();
                var starter = start;
                while (end > starter)
                {
                    months.Add(string.Format("{0}-{1}", starter.ToString("MMM"), starter.ToString("yy")));
                    starter = starter.AddMonths(1);
                }
                List<Project> projectList = (List<Project>)SafetyService.GetProjects();
                projectList.Sort((x, y) => string.Compare(x.Name, y.Name));

                foreach (var project in projectList)
                {

                    current = project.Name;
                    var results = SafetyService.GetProjectInspections(project.Name, start, end);
                    

                    if(results==null) continue;
                    FillProjectWorksheet(pck, project, results, months, start, end);
                    
                }
                if (pck.Workbook.Worksheets.Count == 0)
                {
                    Response.Write("No results for the given period.");
                }
                else
                {
                    
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;  filename=ProjectsReport.xlsx");
                }
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
                Response.Write(ex.StackTrace);
                Response.Write("<br/>");
                Response.Write("Project: " + current);
                Response.End();
            }
            
        }

        private void FillProjectWorksheet(ExcelPackage pck, Project project, ProjectResults results, 
            IList<string> months, DateTime start, DateTime end)
        {

            var ws = pck.Workbook.Worksheets.Add(project.Name);
            ws.defaultColWidth = 7.5;

            ws.Cells["A:Q"].Style.Font.Size = 9;

            var a = ws.Cells[1, 1].Address;
            var b = ws.Cells[1, 14].Address;
            var address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[1, 1].Value = "Project Report " + results.Project + " " + start.ToShortDateString() + " - " + end.ToShortDateString();
            ws.Cells[1, 1].Style.Font.Bold = true;


            a = ws.Cells[1, 15].Address;
            b = ws.Cells[1, 16].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[1, 15].Value = "Last import: " + LastRunDate;
            ws.Cells[1, 15].Style.Font.Bold = true;

            a = ws.Cells[2, 1].Address;
            b = ws.Cells[2, 16].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[2, 1].Value =
                "Graph below plots the monthly man hours and bi-monthly sums of safety team deficiencies with values of D=4, C=8, B=12, A = 16.";
            ws.Cells[2, 1].Style.Font.Italic = true;
            ws.Cells[2, 1].Style.Font.Size = (float)10.5;

            a = ws.Cells[3, 1].Address;
            b = ws.Cells[3, 16].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[3, 1].Value = "Deficiences are logged twice monthly, with sums from 1st to 15th and 16th to 30th plotted below as points and connected to show trends. ";
            ws.Cells[3, 1].Style.Font.Italic = true;
            ws.Cells[3, 1].Style.Font.Size = (float)10.5;

            a = ws.Cells[4, 1].Address;
            b = ws.Cells[4, 16].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[4, 1].Value =
                "Scale at left indicates value of team deficency sums plotted  with scale at right showing monthly man hours.";
            ws.Cells[4, 1].Style.Font.Italic = true;
            ws.Cells[4, 1].Style.Font.Size = (float)10.5;

            
            var Chart1 = new System.Web.UI.DataVisualization.Charting.Chart
            {
                Width = 900,
                Height = 300,
                RenderType = RenderType.ImageTag,
                ImageLocation = "..\\..\\TempImages\\ChartPic_#SEQ(200,30)",
                Palette = ChartColorPalette.BrightPastel
            };

            var title = new Title("Safety Team Deficiencies", Docking.Left,
                              new System.Drawing.Font("Trebuchet MS", 9, System.Drawing.FontStyle.Bold),
                              System.Drawing.Color.FromArgb(26, 59, 105));
            Chart1.Titles.Add(title);

            
            Chart1.ChartAreas.Add("ChartArea1");
            Chart1.ChartAreas[0].InnerPlotPosition = new ElementPosition(3,0,95,95);
            // Enable X axis labels automatic fitting
            Chart1.ChartAreas["ChartArea1"].AxisX.IsLabelAutoFit = true;
            Chart1.ChartAreas["ChartArea1"].AxisX.TextOrientation = TextOrientation.Rotated90;
            
            // Set X axis automatic fitting style
            Chart1.ChartAreas["ChartArea1"].AxisX.LabelAutoFitStyle =
                LabelAutoFitStyles.DecreaseFont | LabelAutoFitStyles.IncreaseFont | LabelAutoFitStyles.WordWrap;
            Chart1.ChartAreas["ChartArea1"].AxisX.LabelStyle.Font = new Font("Trebuchet MS", 8, System.Drawing.FontStyle.Regular);
            Chart1.ChartAreas["ChartArea1"].AxisY.LabelStyle.Font = new Font("Trebuchet MS", 8, System.Drawing.FontStyle.Regular);
            Chart1.ChartAreas["ChartArea1"].AxisY2.LabelStyle.Font = new Font("Trebuchet MS", 8, System.Drawing.FontStyle.Regular);
            Chart1.ChartAreas["ChartArea1"].AxisY2.LabelStyle.Angle = 0;
            Chart1.ChartAreas["ChartArea1"].AxisY2.LabelAutoFitStyle = LabelAutoFitStyles.DecreaseFont | LabelAutoFitStyles.StaggeredLabels;
            Chart1.ChartAreas["ChartArea1"].AxisY.LabelStyle.Angle = 0;
            Chart1.ChartAreas["ChartArea1"].AxisY2.MajorGrid.Enabled = false;
            
            // create a couple of series
            Chart1.Series.Add("GC");
            Chart1.Series.Add("OFPC");
            Chart1.Series.Add("ROCIP");
            Chart1.Series.Add("ManHours");
            //Chart1.Series.Add("Labor");
            Chart1.Series[0].ChartType = SeriesChartType.Line;
            Chart1.Series[1].ChartType = SeriesChartType.Line;
            Chart1.Series[2].ChartType = SeriesChartType.Line;
            Chart1.Series[3].ChartType = SeriesChartType.Area;
            //Chart1.Series[3].ChartType = SeriesChartType.Area;

            Chart1.Series[3].Color = Color.FromArgb(100, Color.Blue);

            Chart1.ChartAreas["ChartArea1"].AxisX.IntervalAutoMode = IntervalAutoMode.FixedCount;
            Chart1.ChartAreas["ChartArea1"].AxisX.Interval = 1;

            Chart1.Series["GC"].BorderWidth = 3;
            Chart1.Series["OFPC"].BorderWidth = 3;
            Chart1.Series["ROCIP"].BorderWidth = 3;

            Chart1.Series["GC"].BorderDashStyle = ChartDashStyle.DashDot;
            Chart1.Series["OFPC"].BorderDashStyle = ChartDashStyle.Dash;

            Chart1.Series["ManHours"].YAxisType = AxisType.Secondary;
            
            var m = start;

            if (results.ManHours != null)
            {
                //Need this because of the extra leading graph point
                //Chart1.Series["ManHours"].Points.AddY(0);
                for (var h = 0; h < months.Count; h++)
                {

                    var y = results.ManHours.Where(x => x.Date == m).ToList();
                    //Add 2 because of the midmonth points
                    if(y.Count == 0)
                    {
                        Chart1.Series["ManHours"].Points.AddY(0);
                        Chart1.Series["ManHours"].Points.AddY(0);
                    }
                    else
                    {
                        Chart1.Series["ManHours"].Points.AddY(y.Sum(x => Double.Parse(x.Value)));
                        Chart1.Series["ManHours"].Points.AddY(y.Sum(x => Double.Parse(x.Value)));
                    }

                    m = m.AddMonths(1);
                }
            }

            //Chart1.Series["Labor"].Color = Color.LightGray;

            for (var i = 0; i < results.GCB.Count; i++)
            {
                //if (results.Labor.Count == results.GCB.Count)
                //    Chart1.Series["Labor"].Points.AddY(results.Labor[i].Hours);



                Chart1.Series["GC"].Points.AddY(results.GCB[i].Total);

                Chart1.Series["GC"].IsValueShownAsLabel = false;



                Chart1.Series["OFPC"].Points.AddY(results.OFPCB[i].Total);
                if((i % 2) == 0)
                {
                    Chart1.Series["OFPC"].Points[i].AxisLabel = results.GCB[i].Name;
                }
                else
                {
                    Chart1.Series["OFPC"].Points[i].AxisLabel = " ";
                }
                Chart1.Series["OFPC"].Points[i].LabelAngle = 30;



                Chart1.Series["OFPC"].IsValueShownAsLabel = false;

                Chart1.Series["ROCIP"].Points.AddY(results.ROCIPB[i].Total);
                Chart1.Series["ROCIP"].IsValueShownAsLabel = false;
            }


            Chart1.BorderSkin.SkinStyle = BorderSkinStyle.None;
            Chart1.BorderWidth = 0;

            
            Chart1.Legends.Add("Legend1");

            Chart1.Legends["Legend1"].Enabled = true;

            var stream = new System.IO.MemoryStream();
            Chart1.SaveImage(stream);

            var image = ws.Drawings.AddPicture("chart3", Image.FromStream(stream));
            image.From.Column = 0;
            image.From.Row = 4;
            image.SetSize(850, 235);

            var row = 17;
            //This is the line where the deficiency table begins
            var deficiency = 17;

            for (var i = 0; i < months.Count; i++)
            {

                ws.Cells[row, i].Value = months[i];
                ws.Cells[row, i].Style.Font.Bold = true;
            }
            row++;
            a = ws.Cells[row, 1].Address;
            b = ws.Cells[row, months.Count].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row, 1].Value = "2. Number of OFPC inspections conducted during Month";
            ws.Cells[row, 1].Style.Font.Bold = true;
            row++;
            for (var i = 0; i < results.OFPC.Count; i++)
            {
                var column = 1;
                ws.Cells[row, i + 1].Value = results.OFPC[i].Total;
                ws.Cells[row, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            }

            row++;
            a = ws.Cells[row, 1].Address;
            b = ws.Cells[row, months.Count].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row, 1].Value = "3. Number of GC inspections conducted during Month";
            ws.Cells[row, 1].Style.Font.Bold = true;
            row++;
            for (var i = 0; i < results.GC.Count; i++)
            {
                var column = 1;
                ws.Cells[row, i + 1].Value = results.GC[i].Total;
                ws.Cells[row, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            }

            row++;
            a = ws.Cells[row, 1].Address;
            b = ws.Cells[row, months.Count].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row, 1].Value = "4. STEP Recognition Eligibility";
            ws.Cells[row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            ws.Cells[row, 1].Style.Font.Bold = true;
            row++;
            for (var i = 0; i < results.Steps.Count; i++)
            {
                var column = 1;
                ws.Cells[row, i + 1].Value = results.Steps[i].Value;
                ws.Cells[row, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            }

            row++;
            a = ws.Cells[row, 1].Address;
            b = ws.Cells[row, 6].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row, 1].Value = "Y Eligible this month";

            a = ws.Cells[row + 1, 1].Address;
            b = ws.Cells[row + 1, 6].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row + 1, 1].Value = "NE-1 Not Eligible - Lack Of Required Entries";

            a = ws.Cells[row + 2, 1].Address;
            b = ws.Cells[row + 2, 6].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row + 2, 1].Value = "NE-2 Not Eligible - Exceeds Recorded Injuries";

            a = ws.Cells[row, 7].Address;
            b = ws.Cells[row, 12].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row, 7].Value = "NE-3 Not Eligible - Exceeds Deficient Conditions";

            a = ws.Cells[row + 1, 7].Address;
            b = ws.Cells[row + 1, 12].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row + 1, 7].Value = "NE-4 Not Eligible - C202 Submission";

            a = ws.Cells[row + 2, 7].Address;
            b = ws.Cells[row + 2, 12].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row + 2, 7].Value = "NE-5 Not Eligible - GL Claim Submission";

            a = ws.Cells[row, 13].Address;
            b = ws.Cells[row, 16].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row, 13].Value = "NE-6 Not Eligible - PSC Call";

            a = ws.Cells[row + 2, 13].Address;
            b = ws.Cells[row + 2, 16].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row + 2, 13].Value = "Total Project Claims and Costs";

            

            row += 3;

            a = ws.Cells[row, 1].Address;
            b = ws.Cells[row, months.Count].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row, 1].Value =
                "5. ROCIP General Liability, Workers, Compensations, Claims, Info, Amounts & Numbers";
            ws.Cells[row, 1].Style.Font.Bold = true;

            ws.Cells[row, months.Count + 1].Value = "08-11";
            ws.Cells[row, months.Count + 1].Style.Font.Bold = true;
            ws.Cells[row, months.Count + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[row, months.Count + 2].Value = "2012";
            ws.Cells[row, months.Count + 2].Style.Font.Bold = true;
            ws.Cells[row, months.Count + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[row, months.Count + 3].Value = "2013";
            ws.Cells[row, months.Count + 3].Style.Font.Bold = true;
            ws.Cells[row, months.Count + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[row, months.Count + 4].Value = "Total";
            ws.Cells[row, months.Count + 4].Style.Font.Bold = true;
            ws.Cells[row, months.Count + 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            row++;
            
            for (var i = 0; i < results.Claims.Count; i++)
            {
                var column = 1;
                ws.Cells[row, i + 1].Value = "$" + (int)Math.Round(results.Claims[i].Amount, 0);
                ws.Cells[row, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                ws.Cells[row + 1, i + 1].Value = results.Claims[i].Count;
                ws.Cells[row + 1, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            }

            ws.Cells[row, months.Count + 1].Value = "$" + (int)results.YearTotal;
            ws.Cells[row, months.Count + 1].Style.Font.Bold = true;
            ws.Cells[row, months.Count + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[row, months.Count + 2].Value = "$" + (int)results.YearTotal1;
            ws.Cells[row, months.Count + 2].Style.Font.Bold = true;
            ws.Cells[row, months.Count + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[row, months.Count + 3].Value = "$" + (int)results.YearTotal2;
            ws.Cells[row, months.Count + 3].Style.Font.Bold = true;
            ws.Cells[row, months.Count + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[row, months.Count + 4].Value = "$" + (int) (results.YearTotal + results.YearTotal1 + results.YearTotal2);
            ws.Cells[row, months.Count + 4].Style.Font.Bold = true;
            ws.Cells[row, months.Count + 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[row + 1, months.Count + 1].Value = results.YearCount;
            ws.Cells[row + 1, months.Count + 1].Style.Font.Bold = true;
            ws.Cells[row + 1, months.Count + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;


            ws.Cells[row + 1, months.Count + 2].Value = results.YearCount1;
            ws.Cells[row + 1, months.Count + 2].Style.Font.Bold = true;
            ws.Cells[row + 1, months.Count + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[row + 1, months.Count + 3].Value = results.YearCount2;
            ws.Cells[row + 1, months.Count + 3].Style.Font.Bold = true;
            ws.Cells[row + 1, months.Count + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[row + 1, months.Count + 4].Value = results.YearCount + results.YearCount1 + results.YearCount2;
            ws.Cells[row + 1, months.Count + 4].Style.Font.Bold = true;
            ws.Cells[row + 1, months.Count + 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;


            row += 2;

            a = ws.Cells[row, 1].Address;
            b = ws.Cells[row, 4].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row, 1].Value = "6. Lagging Safety Indicators";

            ws.Cells[row, 1].Style.Font.Bold = true;

            //Get OSHA Averages for selected endyear
            var endyear = Convert.ToInt32(end.Year);
            var osha = SafetyService.GetOSHAAverage(endyear);
            var trirAverage = osha.TRIRAverage;
            var dafwAverage = osha.DAFWAverage;

            a = ws.Cells[row, 10].Address;
            b = ws.Cells[row, 13].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row, 10].Value = string.Format("{0} OSHA National Averages", endyear);
            ws.Cells[row, 10].Style.Font.Bold = true;
            ws.Cells[row, 14].Value = trirAverage;
            ws.Cells[row, 14].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            ws.Cells[row, 14].Style.Font.Bold = true;
            ws.Cells[row, 15].Value = dafwAverage;
            ws.Cells[row, 15].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            ws.Cells[row, 15].Style.Font.Bold = true;

            row++;

            a = ws.Cells[row, 1].Address;
            b = ws.Cells[row, 8].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row, 1].Value = "Project";
            ws.Cells[row, 1].Style.Font.Bold = true;

            a = ws.Cells[row + 1, 1].Address;
            b = ws.Cells[row + 1, 8].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;

            ws.Cells[row + 1, 1].Value = results.Project;

            ws.Cells[row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            ws.Cells[row + 1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

            //a = ws.Cells[row, 3].Address;
            //b = ws.Cells[row, 4].Address;
            //address = string.Format("{0}:{1}", a, b);
            //ws.Cells[address].Merge = true;
            ws.Cells[row, 9].Value = "Region";
            ws.Cells[row, 9].Style.Font.Bold = true;
            ws.Cells[row, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            ws.Cells[row + 1, 9].Value = project.Region;
            ws.Cells[row + 1, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            
            //a = ws.Cells[row, 5].Address;
            //b = ws.Cells[row, 6].Address;
            //address = string.Format("{0}:{1}", a, b);
            //ws.Cells[address].Merge = true;

            ws.Cells[row, 10].Value = "Labor Hours";
            ws.Cells[row, 10].Style.WrapText = true;
            ws.Cells[row, 10].Style.Font.Bold = true;
            ws.Cells[row, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            if (results.RIR != null)
            {
                ws.Cells[row + 1, 10].Value = results.RIR.LaborHours;
                ws.Cells[row + 1, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            }
            //a = ws.Cells[row, 7].Address;
            //b = ws.Cells[row, 8].Address;
            //address = string.Format("{0}:{1}", a, b);
            //ws.Cells[address].Merge = true;
            ws.Cells[row, 11].Value = "First Aids";
            ws.Cells[row, 11].Style.WrapText = true;
            ws.Cells[row, 11].Style.Font.Bold = true;
            ws.Cells[row, 11].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            if (results.RIR != null)
            {
                ws.Cells[row + 1, 11].Value = results.RIR.FirstAid;
                ws.Cells[row + 1, 11].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            }
            //a = ws.Cells[row, 9].Address;
            //b = ws.Cells[row, 10].Address;
            //address = string.Format("{0}:{1}", a, b);
            //ws.Cells[address].Merge = true;
            ws.Cells[row, 12].Value = "Osha Rec";
            ws.Cells[row, 12].Style.WrapText = true;
            ws.Cells[row, 12].Style.Font.Bold = true;
            ws.Cells[row, 12].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            if (results.RIR != null)
            {
                ws.Cells[row + 1, 12].Value = results.RIR.OSHARcrd;
                ws.Cells[row + 1, 12].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            }
            //a = ws.Cells[row, 11].Address;
            //b = ws.Cells[row, 12].Address;
            //address = string.Format("{0}:{1}", a, b);
            //ws.Cells[address].Merge = true;
            ws.Cells[row, 13].Value = "DAFW";
            ws.Cells[row, 13].Style.Font.Bold = true;
            ws.Cells[row, 13].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            if (results.RIR != null)
            {
                ws.Cells[row + 1, 13].Value = results.RIR.DAFW;
                ws.Cells[row + 1, 13].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            }
            //a = ws.Cells[row, 13].Address;
            //b = ws.Cells[row, 14].Address;
            //address = string.Format("{0}:{1}", a, b);
            //ws.Cells[address].Merge = true;
            ws.Cells[row, 14].Value = "TRIR Rate";
            ws.Cells[row, 14].Style.WrapText = true;
            ws.Cells[row, 14].Style.Font.Bold = true;
            ws.Cells[row, 14].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            if (results.RIR != null)
            {
                ws.Cells[row + 1, 14].Value = Math.Round(results.RIR.TRIR, 1);
                ws.Cells[row + 1, 14].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            }
            //a = ws.Cells[row, 15].Address;
            //b = ws.Cells[row, 16].Address;
            //address = string.Format("{0}:{1}", a, b);
            //ws.Cells[address].Merge = true;
            ws.Cells[row, 15].Value = "DAFW Rate";
            ws.Cells[row, 15].Style.WrapText = true;
            ws.Cells[row, 15].Style.Font.Bold = true;
            ws.Cells[row, 15].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            if (results.RIR != null)
            {
                ws.Cells[row + 1, 15].Value = Math.Round(results.RIR.DAFWIR, 1);
                ws.Cells[row + 1, 15].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            }
            //a = ws.Cells[row, 17].Address;
            //b = ws.Cells[row, 18].Address;
            //address = string.Format("{0}:{1}", a, b);
            //ws.Cells[address].Merge = true;
            ws.Cells[row, 16].Value = "Data Date";
            ws.Cells[row, 16].Style.WrapText = true;
            ws.Cells[row, 16].Style.Font.Bold = true;
            ws.Cells[row, 16].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            ws.Cells[row + 1, 16].Value = months[months.Count - 1];
            ws.Cells[row + 1, 16].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;


            a = ws.Cells[row + 2, 1].Address;
            b = ws.Cells[row + 5, 16].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[row + 2, 1].Value = "OFPC Comments";
            ws.Cells[row + 2, 1].Style.Font.Bold = true;
            ws.Cells[row + 2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            ws.Cells[row + 2, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Top;



            var c = months.Count + 1;

            a = ws.Cells[deficiency, c].Address;
            b = ws.Cells[deficiency, c + 2].Address;
            address = string.Format("{0}:{1}", a, b);
            ws.Cells[address].Merge = true;
            ws.Cells[deficiency, c].Value = "SafetyNet Deficient Conditions";
            ws.Cells[deficiency, c].Style.Font.Bold = true;
            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

            ws.Cells[deficiency, c + 3].Value = end.AddMonths(-1).ToString("MMMM");
            ws.Cells[deficiency, c + 3].Style.Font.Bold = true;
            deficiency++;

            ws.Cells[deficiency, c].Value = "Severity";
            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            ws.Cells[deficiency, c].Style.Font.Bold = true;

            ws.Cells[deficiency, c + 1].Value = "ROCIP";
            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            ws.Cells[deficiency, c + 1].Style.Font.Bold = true;

            ws.Cells[deficiency, c + 2].Value = "OFPC";
            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            ws.Cells[deficiency, c + 2].Style.Font.Bold = true;

            ws.Cells[deficiency, c + 3].Value = "GC";
            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            ws.Cells[deficiency, c + 3].Style.Font.Bold = true;

            deficiency++;

            ws.Cells[deficiency, c].Value = "A";
            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            deficiency++;

            ws.Cells[deficiency, c].Value = "B";
            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            deficiency++;

            ws.Cells[deficiency, c].Value = "C";
            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            deficiency++;

            ws.Cells[deficiency, c].Value = "D";
            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            deficiency++;

            ws.Cells[deficiency, c].Value = "Total";
            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            deficiency = 19;


            ws.Cells[deficiency, c + 1].Value = results.ROCIPSeverityA;
            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[deficiency, c + 2].Value = results.OFPCSeverityA;
            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[deficiency, c + 3].Value = results.GCSeverityA;
            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            deficiency++;


            ws.Cells[deficiency, c + 1].Value = results.ROCIPSeverityB;
            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[deficiency, c + 2].Value = results.OFPCSeverityB;
            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[deficiency, c + 3].Value = results.GCSeverityB;
            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            deficiency++;

            ws.Cells[deficiency, c + 1].Value = results.ROCIPSeverityC;
            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[deficiency, c + 2].Value = results.OFPCSeverityC;
            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[deficiency, c + 3].Value = results.GCSeverityC;
            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            deficiency++;

            ws.Cells[deficiency, c + 1].Value = results.ROCIPSeverityD;
            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[deficiency, c + 2].Value = results.OFPCSeverityD;
            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[deficiency, c + 3].Value = results.GCSeverityD;
            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            deficiency++;
            //SUM

            ws.Cells[deficiency, c + 1].Value = results.ROCIPSeverityA + results.ROCIPSeverityB + results.ROCIPSeverityC + results.ROCIPSeverityD;
            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[deficiency, c + 2].Value = results.OFPCSeverityA + results.OFPCSeverityB + results.OFPCSeverityC + results.OFPCSeverityD;
            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

            ws.Cells[deficiency, c + 3].Value = results.GCSeverityA + results.GCSeverityB + results.GCSeverityC + results.GCSeverityD;
            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        }

        #region OldBulkExport

        //public void AllProjectsExport(string startmonth, string startyear, string endmonth, string endyear, string mode)
        //{


        //    var start = new DateTime(Convert.ToInt32(startyear), Convert.ToInt32(startmonth), 1);
        //    var end = new DateTime(Convert.ToInt32(endyear), Convert.ToInt32(endmonth), 1);
        //    end = end.AddMonths(1);
        //    var pck = new ExcelPackage();
        //    var current = string.Empty;

        //    try
        //    {
        //        var months = new List<string>();
        //        var starter = start;
        //        while (end > starter)
        //        {
        //            months.Add(string.Format("{0}-{1}", starter.ToString("MMM"), starter.ToString("yy")));
        //            starter = starter.AddMonths(1);
        //        }
        //        foreach (var project in Projects)
        //        {

        //            current = project;
        //            var results = SafetyService.GetProjectInspections(project, start, end);


        //            if (results == null) continue;

        //            var ws = pck.Workbook.Worksheets.Add(project);
        //            ws.defaultColWidth = 7.5;


        //            var a = ws.Cells[1, 1].Address;
        //            var b = ws.Cells[1, months.Count].Address;
        //            var address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[1, 1].Value = "Project Report " + results.Project + " " + start.ToShortDateString() + " - " +
        //                                   end.ToShortDateString();
        //            ws.Cells[1, 1].Style.Font.Bold = true;

        //            var Chart1 = new System.Web.UI.DataVisualization.Charting.Chart
        //            {
        //                Width = 950,
        //                Height = 300,
        //                RenderType = RenderType.ImageTag,
        //                ImageLocation = "..\\..\\TempImages\\ChartPic_#SEQ(200,30)",
        //                Palette = ChartColorPalette.BrightPastel
        //            };

        //            var t = new Title("Statewide Deficiency Category", Docking.Top,
        //                              new System.Drawing.Font("Trebuchet MS", 11, System.Drawing.FontStyle.Bold),
        //                              System.Drawing.Color.FromArgb(26, 59, 105));
        //            Chart1.Titles.Add(t);
        //            Chart1.ChartAreas.Add("ChartArea1");

        //            // Enable X axis labels automatic fitting

        //            Chart1.ChartAreas["ChartArea1"].AxisX.IsLabelAutoFit = true;
        //            Chart1.ChartAreas["ChartArea1"].AxisX.TextOrientation = TextOrientation.Rotated90;
        //            // Set X axis automatic fitting style
        //            Chart1.ChartAreas["ChartArea1"].AxisX.LabelAutoFitStyle =
        //                LabelAutoFitStyles.DecreaseFont | LabelAutoFitStyles.IncreaseFont | LabelAutoFitStyles.WordWrap;
        //            // create a couple of series
        //            Chart1.Series.Add("GC");
        //            Chart1.Series.Add("OFPC");
        //            Chart1.Series.Add("ROCIP");
        //            //Chart1.Series.Add("Labor");
        //            Chart1.Series[0].ChartType = SeriesChartType.Line;
        //            Chart1.Series[1].ChartType = SeriesChartType.Line;
        //            Chart1.Series[2].ChartType = SeriesChartType.Line;
        //            //Chart1.Series[3].ChartType = SeriesChartType.Area;

        //            Chart1.ChartAreas["ChartArea1"].AxisX.IntervalAutoMode = IntervalAutoMode.FixedCount;
        //            Chart1.ChartAreas["ChartArea1"].AxisX.Interval = 1;

        //            Chart1.Series["GC"].BorderWidth = 3;
        //            Chart1.Series["OFPC"].BorderWidth = 3;
        //            Chart1.Series["ROCIP"].BorderWidth = 3;

        //            Chart1.Series["GC"].BorderDashStyle = ChartDashStyle.DashDot;
        //            Chart1.Series["OFPC"].BorderDashStyle = ChartDashStyle.Dash;


        //            Chart1.Series["OFPC"].Font = new Font("Trebuchet MS", 9, System.Drawing.FontStyle.Bold);

        //            //Chart1.Series["Labor"].Color = Color.LightGray;

        //            for (var i = 0; i < results.GCB.Count; i++)
        //            {
        //                //if (results.Labor.Count == results.GCB.Count)
        //                //    Chart1.Series["Labor"].Points.AddY(results.Labor[i].Hours);



        //                Chart1.Series["GC"].Points.AddY(results.GCB[i].Total);

        //                Chart1.Series["GC"].IsValueShownAsLabel = false;



        //                Chart1.Series["OFPC"].Points.AddY(results.OFPCB[i].Total);
        //                Chart1.Series["OFPC"].Points[i].AxisLabel = results.GCB[i].Name;
        //                Chart1.Series["OFPC"].Points[i].LabelAngle = 30;


        //                Chart1.Series["OFPC"].IsValueShownAsLabel = false;

        //                Chart1.Series["ROCIP"].Points.AddY(results.ROCIPB[i].Total);
        //                Chart1.Series["ROCIP"].IsValueShownAsLabel = false;
        //            }


        //            Chart1.BorderSkin.SkinStyle = BorderSkinStyle.None;
        //            Chart1.BorderWidth = 0;

        //            Chart1.Legends.Add("Legend1");

        //            // show legend based on check box value
        //            Chart1.Legends["Legend1"].Enabled = true;

        //            var stream = new System.IO.MemoryStream();
        //            Chart1.SaveImage(stream);

        //            var image = ws.Drawings.AddPicture("chart3", Image.FromStream(stream));
        //            image.From.Column = 0;
        //            image.From.Row = 3;
        //            image.SetSize(950, 300);

        //            var row = 20;
        //            var deficiency = 20;


        //            for (var i = 0; i < months.Count; i++)
        //            {

        //                ws.Cells[row, i + 1].Value = months[i];
        //                ws.Cells[row, i + 1].Style.Font.Bold = true;
        //            }
        //            row++;
        //            a = ws.Cells[row, 1].Address;
        //            b = ws.Cells[row, months.Count].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 1].Value = "2. Number of OFPC inspections conducted during Month";
        //            ws.Cells[row, 1].Style.Font.Bold = true;
        //            row++;
        //            for (var i = 0; i < results.OFPC.Count; i++)
        //            {
        //                var column = 1;
        //                ws.Cells[row, i + 1].Value = results.OFPC[i].Total;
        //                ws.Cells[row, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            }

        //            row++;
        //            a = ws.Cells[row, 1].Address;
        //            b = ws.Cells[row, months.Count].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 1].Value = "3. Number of GC inspections conducted during Month";
        //            ws.Cells[row, 1].Style.Font.Bold = true;
        //            row++;
        //            for (var i = 0; i < results.GC.Count; i++)
        //            {
        //                var column = 1;
        //                ws.Cells[row, i + 1].Value = results.GC[i].Total;
        //                ws.Cells[row, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            }

        //            row++;
        //            a = ws.Cells[row, 1].Address;
        //            b = ws.Cells[row, months.Count].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 1].Value = "4. STEP Recognition Eligibility";
        //            ws.Cells[row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
        //            ws.Cells[row, 1].Style.Font.Bold = true;
        //            row++;
        //            for (var i = 0; i < results.Steps.Count; i++)
        //            {
        //                var column = 1;
        //                ws.Cells[row, i + 1].Value = results.Steps[i].Value;
        //                ws.Cells[row, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            }

        //            row++;
        //            a = ws.Cells[row, 1].Address;
        //            b = ws.Cells[row, 6].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 1].Value = "Y Eligible this month";

        //            a = ws.Cells[row + 1, 1].Address;
        //            b = ws.Cells[row + 1, 6].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row + 1, 1].Value = "NE-1 Not Eligible - Lack Of Required Entries";

        //            a = ws.Cells[row + 2, 1].Address;
        //            b = ws.Cells[row + 2, 6].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row + 2, 1].Value = "NE-2 Not Eligible - Exceeds Recorded Injuries";

        //            a = ws.Cells[row, 7].Address;
        //            b = ws.Cells[row, 13].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 7].Value = "NE-3 Not Eligible - Exceeds Deficient Conditions";

        //            a = ws.Cells[row + 1, 7].Address;
        //            b = ws.Cells[row + 1, 13].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row + 1, 7].Value = "NE-4 Not Eligible - C202 Submission";

        //            a = ws.Cells[row, 14].Address;
        //            b = ws.Cells[row, 20].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 14].Value = "NE-5 Not Eligible - GL Claim Submission";

        //            a = ws.Cells[row + 1, 14].Address;
        //            b = ws.Cells[row + 1, 20].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row + 1, 14].Value = "NE-6 Not Eligible - PSC Call";

        //            row += 3;





        //            a = ws.Cells[row, 1].Address;
        //            b = ws.Cells[row, months.Count].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 1].Value =
        //                "5. ROCIP General Liability, Workers, Compensations, Claims, Info, Amounts & Numbers";
        //            ws.Cells[row, 1].Style.Font.Bold = true;

        //            ws.Cells[row, months.Count + 2].Value = "2010";
        //            ws.Cells[row, months.Count + 2].Style.Font.Bold = true;
        //            ws.Cells[row, months.Count + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[row, months.Count + 3].Value = "2011";
        //            ws.Cells[row, months.Count + 3].Style.Font.Bold = true;
        //            ws.Cells[row, months.Count + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[row, months.Count + 4].Value = "Total";
        //            ws.Cells[row, months.Count + 4].Style.Font.Bold = true;
        //            ws.Cells[row, months.Count + 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            row++;
        //            for (var i = 0; i < results.Claims.Count; i++)
        //            {
        //                var column = 1;
        //                ws.Cells[row, i + 1].Value = string.Format("{0:C}", results.Claims[i].Amount);
        //                ws.Cells[row, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //                ws.Cells[row + 1, i + 1].Value = results.Claims[i].Count;
        //                ws.Cells[row + 1, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            }

        //            ws.Cells[row, months.Count + 2].Value = results.YearTotal1;
        //            ws.Cells[row, months.Count + 2].Style.Font.Bold = true;
        //            ws.Cells[row, months.Count + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[row, months.Count + 3].Value = results.YearTotal2;
        //            ws.Cells[row, months.Count + 3].Style.Font.Bold = true;
        //            ws.Cells[row, months.Count + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[row, months.Count + 4].Value = results.YearTotal1 + results.YearTotal2;
        //            ws.Cells[row, months.Count + 4].Style.Font.Bold = true;
        //            ws.Cells[row, months.Count + 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;


        //            ws.Cells[row + 1, months.Count + 2].Value = results.YearCount1;
        //            ws.Cells[row + 1, months.Count + 2].Style.Font.Bold = true;
        //            ws.Cells[row + 1, months.Count + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[row + 1, months.Count + 3].Value = results.YearCount2;
        //            ws.Cells[row + 1, months.Count + 3].Style.Font.Bold = true;
        //            ws.Cells[row + 1, months.Count + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[row + 1, months.Count + 4].Value = results.YearCount1 + results.YearCount2;
        //            ws.Cells[row + 1, months.Count + 4].Style.Font.Bold = true;
        //            ws.Cells[row + 1, months.Count + 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;


        //            row += 2;

        //            a = ws.Cells[row, 1].Address;
        //            b = ws.Cells[row, 4].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 1].Value = "6. Lagging Safety Indicators";

        //            ws.Cells[row, 1].Style.Font.Bold = true;

        //            a = ws.Cells[row, 5].Address;
        //            b = ws.Cells[row, 6].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 5].Value = "09 OSHA National Averages";
        //            ws.Cells[row, 5].Style.Font.Bold = true;
        //            ws.Cells[row, 7].Value = "4.3";
        //            ws.Cells[row, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            ws.Cells[row, 7].Style.Font.Bold = true;
        //            ws.Cells[row, 8].Value = "1.6";
        //            ws.Cells[row, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            ws.Cells[row, 8].Style.Font.Bold = true;

        //            row++;

        //            a = ws.Cells[row, 1].Address;
        //            b = ws.Cells[row, 2].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 1].Value = "Project";
        //            ws.Cells[row + 1, 1].Value = results.Project;

        //            ws.Cells[row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            ws.Cells[row + 1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            a = ws.Cells[row, 3].Address;
        //            b = ws.Cells[row, 4].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 3].Value = "Region";
        //            ws.Cells[row, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            if (results.RIR != null)
        //            {
        //                ws.Cells[row + 1, 3].Value = results.RIR.Region;
        //                ws.Cells[row + 1, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            }
        //            a = ws.Cells[row, 5].Address;
        //            b = ws.Cells[row, 6].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 5].Value = "Labor Hours";
        //            ws.Cells[row, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            if (results.RIR != null)
        //            {
        //                ws.Cells[row + 1, 5].Value = results.RIR.LaborHours;
        //                ws.Cells[row + 1, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            }
        //            a = ws.Cells[row, 7].Address;
        //            b = ws.Cells[row, 8].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 7].Value = "First Aids";
        //            ws.Cells[row, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            if (results.RIR != null)
        //            {
        //                ws.Cells[row + 1, 7].Value = results.RIR.FirstAid;
        //                ws.Cells[row + 1, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            }
        //            a = ws.Cells[row, 9].Address;
        //            b = ws.Cells[row, 10].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 9].Value = "Osha Rec";
        //            ws.Cells[row, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            if (results.RIR != null)
        //            {
        //                ws.Cells[row + 1, 9].Value = results.RIR.OSHARcrd;
        //                ws.Cells[row + 1, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            }
        //            a = ws.Cells[row, 11].Address;
        //            b = ws.Cells[row, 12].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 11].Value = "DAFW";
        //            ws.Cells[row, 11].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            if (results.RIR != null)
        //            {
        //                ws.Cells[row + 1, 11].Value = results.RIR.DAFW;
        //                ws.Cells[row + 1, 11].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            }
        //            a = ws.Cells[row, 13].Address;
        //            b = ws.Cells[row, 14].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 13].Value = "TRIR Rate";
        //            ws.Cells[row, 13].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            if (results.RIR != null)
        //            {
        //                ws.Cells[row + 1, 13].Value = Math.Round(results.RIR.TRIR, 1);
        //                ws.Cells[row + 1, 13].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            }
        //            a = ws.Cells[row, 15].Address;
        //            b = ws.Cells[row, 16].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 15].Value = "DAFW Rate";
        //            ws.Cells[row, 15].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            if (results.RIR != null)
        //            {
        //                ws.Cells[row + 1, 15].Value = Math.Round(results.RIR.DAFWIR, 1);
        //                ws.Cells[row + 1, 15].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            }
        //            a = ws.Cells[row, 17].Address;
        //            b = ws.Cells[row, 18].Address;
        //            address = string.Format("{0}:{1}", a, b);
        //            ws.Cells[address].Merge = true;
        //            ws.Cells[row, 17].Value = "Data Date";
        //            ws.Cells[row, 17].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            ws.Cells[row + 1, 17].Value = months[months.Count - 1];
        //            ws.Cells[row + 1, 17].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;



        //            var c = months.Count + 1;


        //            ws.Cells[deficiency, c].Value = "SafetyNet Deficient Conditions";
        //            ws.Cells[deficiency, c].Style.Font.Bold = true;
        //            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            deficiency++;

        //            ws.Cells[deficiency, c].Value = "Severity";
        //            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 1].Value = "ROCIP";
        //            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 2].Value = "OFPC";
        //            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 3].Value = "GC";
        //            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            deficiency++;

        //            ws.Cells[deficiency, c].Value = "A";
        //            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            deficiency++;

        //            ws.Cells[deficiency, c].Value = "B";
        //            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            deficiency++;

        //            ws.Cells[deficiency, c].Value = "C";
        //            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            deficiency++;

        //            ws.Cells[deficiency, c].Value = "D";
        //            ws.Cells[deficiency, c].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            deficiency = 22;


        //            ws.Cells[deficiency, c + 1].Value = results.ROCIPSeverityD;
        //            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 2].Value = results.OFPCSeverityD;
        //            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 3].Value = results.GCSeverityD;
        //            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            deficiency++;


        //            ws.Cells[deficiency, c + 1].Value = results.ROCIPSeverityC;
        //            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 2].Value = results.OFPCSeverityC;
        //            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 3].Value = results.OFPCSeverityC;
        //            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            deficiency++;

        //            ws.Cells[deficiency, c + 1].Value = results.ROCIPSeverityB;
        //            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 2].Value = results.OFPCSeverityB;
        //            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 3].Value = results.GCSeverityB;
        //            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            deficiency++;

        //            ws.Cells[deficiency, c + 1].Value = results.ROCIPSeverityA;
        //            ws.Cells[deficiency, c + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 2].Value = results.OFPCSeverityA;
        //            ws.Cells[deficiency, c + 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //            ws.Cells[deficiency, c + 3].Value = results.GCSeverityA;
        //            ws.Cells[deficiency, c + 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Response.Write(ex.Message);
        //        Response.Write("<br/>");
        //        Response.Write("Project: " + current);
        //        Response.End();
        //    }
        //    Response.BinaryWrite(pck.GetAsByteArray());
        //    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        //    Response.AddHeader("content-disposition", "attachment;  filename=ProjectsReport.xlsx");
        //}
        
        #endregion

       
    }
}